﻿/**
//  ArchiveEntity.cs
//  Marquee Moment
//  Copyright 2011 Systematix Technocrates. All rights reserved.
**/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using NLog;


namespace MarqueeMoment.Entities
{
    /// <summary>
    /// This is the class to keep all the transaction method from tblArchive
    /// </summary>
    public class ArchiveEntity
    {
        private static Logger logger = LogManager.GetCurrentClassLogger();
        #region Fields

        private Int32 archiveId;
        private Int32 imageId;
        private DateTime  archiveTime;

        private List<ArchiveEntity> archivelist;

        #endregion Fields

        #region Constructors

        public ArchiveEntity()
            : base()
        {
            archiveId = 0;
            imageId = 0;
            var timeZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
            archiveTime = TimeZoneInfo.ConvertTime(DateTime.Now, timeZone);

            archivelist = new List<ArchiveEntity>();
        }


        #endregion Constructors

        #region Properties

        public Int32 ArchiveId
        {
            get { return archiveId; }
            set { archiveId = value; }
        }

        public Int32 ImageId
        {
            get { return imageId; }
            set { imageId = value; }
        }


        public DateTime ArchiveTime
        {
            get { return archiveTime; }
            set { archiveTime = value; }
        }

        #endregion Properties

        #region Methods

        #region Public

        #endregion Public

        #region Private

        #endregion Private

        #endregion Methods

        #region Static methods

        #region Public

        /// <summary>
        /// To select all the records from the tblArchive table
        /// </summary>
        /// <returns></returns>     
        public static List<ArchiveEntity> SelectAll()
        {
            List<ArchiveEntity> archivelist = null;
            ArchiveEntity archive = null;
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectAllArchive]";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        archivelist = new List<ArchiveEntity>();
                        for (int count = 0; count < dataSet.Tables[0].Rows.Count; count++)
                        {
                            archive = new ArchiveEntity();
                            archive.ArchiveId = Convert.ToInt32(dataSet.Tables[0].Rows[count][tblArchive.ArchiveId]);
                            archive.ImageId = Convert.ToInt32(dataSet.Tables[0].Rows[count][tblArchive.ImageId]);
                            archive.ArchiveTime = Convert.ToDateTime(dataSet.Tables[0].Rows[count][tblArchive.ArchiveTime]);
                            archivelist.Add(archive);
                        }
                    }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return archivelist;
        }

        /// <summary>
        /// To select all the records from tblArchive and return Dataset of the records
        /// </summary>
        /// <returns></returns>
        public static DataSet SelectAllArchive()
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectAllArchive]";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    return dataSet;

            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All Archive");
            }
            finally
            {
                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return null;
        }


        /// <summary>
        /// To select all the Archive image from tblImage and return Dataset of the records
        /// </summary>
        /// <returns></returns>
        public static DataTable SelectAllArchiveWithImage()
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectAllArchiveWithImage]";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                {
                    DataTable myTable = new DataTable();
                    myTable.Columns.AddRange(new DataColumn[] { new DataColumn("ArchiveId"), new DataColumn("ImageData"), new DataColumn("ArchiveTime") });
                    myTable.Columns["ImageData"].DataType= dataSet.Tables[0].Columns["ImageData"].DataType;
                    
                    for (Int32 i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                    {
                        DateTime dt = (DateTime)dataSet.Tables[0].Rows[i]["ArchiveTime"];
                        myTable.Rows.Add(dataSet.Tables[0].Rows[i]["ArchiveId"], dataSet.Tables[0].Rows[i]["ImageData"], dt.ToString("dddd, MMMM d - h:mm tt"));                       
                    }
                    myTable.AcceptChanges();

                    return myTable;
                }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All Archive With Image");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return null;
        }


        /// <summary>
        /// To select all the Archive image from tblImage and return Dataset of the records
        /// </summary>
        /// <returns></returns>
        public static DataSet SelectAllArchiveWithImages()
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectAllArchiveWithImage]";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    return dataSet;
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All Archive With Image");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return null;
        }


        /// <summary>
        /// To select all the Archive image from tblImage and return Dataset of the records
        /// </summary>
        /// <returns></returns>
        public static DataSet SelectAllArchiveWithImageByFilter(DateTime StartDate,DateTime EndDate)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectAllArchiveWithImageByFilter]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + "StartDate", StartDate);
            cmd.Parameters.AddWithValue("@" + "EndDate", EndDate);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    return dataSet;
                
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All Archive With Image");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return null;
        }

        /// <summary>
        /// To delete the records of the tblArchive table
        /// </summary>
        /// <param name="archiveId">archive Id is the Id to delete the Particular Record /param>
        public static void Delete(int archiveId)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spDeleteArchive]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblArchive.ArchiveId, archiveId);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Delete Archive");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }

        /// <summary>
        /// To delete the records of the tblArchive table
        /// </summary>
        /// <param name="scheduleId">Schedule Id is the Id to delete the Particular Record /param>
        public static void DeleteOldArchive(DateTime currentDate)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spDeleteOldArchive]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + "CurrentDate", currentDate);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Delete Archive Image");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }


        /// <summary>
        /// To update the record of the tblArchive
        /// </summary>
        /// <param name="archive">This contains the archive Id to update the record</param>
        public static void Update(ArchiveEntity archive)
        {

            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spUpdateArchive]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblArchive.ArchiveId, archive.ArchiveId);
            cmd.Parameters.AddWithValue("@" + tblArchive.ImageId, archive.ImageId);
            cmd.Parameters.AddWithValue("@" + tblArchive.ArchiveTime, archive.ArchiveTime);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);

            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Update Archive");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }
        /// <summary>
        /// To insert the record in tblArchive
        /// </summary>
        /// <param name="archive">It contains the information to insert the record </param>
        public static void Insert(ArchiveEntity archive)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spInsertArchive]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblArchive.ImageId, archive.ImageId);
            cmd.Parameters.AddWithValue("@" + tblArchive.ArchiveTime, archive.ArchiveTime);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);

            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Insert Archive");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }


        /// <summary>
        /// To select particular record from tblArchive and return ArchiveEntity
        /// </summary>
        /// <returns></returns>
        public static ArchiveEntity Select(Int32 archiveId)
        {
            ArchiveEntity archive = null;
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectArchiveById]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblArchive.ArchiveId, archiveId);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        archive = new ArchiveEntity();
                        archive.ArchiveId = Convert.ToInt32(dataSet.Tables[0].Rows[0][tblArchive.ArchiveId]);
                        archive.ImageId = Convert.ToInt32(dataSet.Tables[0].Rows[0][tblArchive.ImageId]);
                        archive.ArchiveTime = Convert.ToDateTime(dataSet.Tables[0].Rows[0][tblArchive.ArchiveTime]);
                    }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select ArchiveEntity");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return archive;
        }

        #endregion Public

        #endregion Static methods
    }
}
